package Database;

import homework_3_by_ly.ShazamHash;

import java.sql.*;
import java.util.ArrayList;

/**
 * Created by WeiZehao on 16/11/7.
 * This class can contact with the database and exchange information.
 */

public class DBManager
{
    private static final String url = "jdbc:mysql://localhost:3306/songfingerDB?" +
            "useUnicode=true&characterEncoding=utf-8&useSSL=false" +
            "&useServerPrepStmts=false&rewriteBatchedStatements=true";
    private static final String sql = "SELECT LAST_INSERT_ID()";

    private Connection con;
    private ResultSet resultSet;
    private PreparedStatement preStmt1;
    private PreparedStatement preStmt2;
    private PreparedStatement preStmt3;
    private PreparedStatement preStmt4;

    private String sql_song;                // insert information into table song
    private String sql_finger;              // insert information into table finger
    private String sql_songfinger;          // insert information into table finger_song
    private String sql_select;              // select information from finger
    private String sql_select2;             // select information from finger_song
    private int song_id;                    // store foreign key song_id
    private int finger_id;                  // store foreign key finger_id
    private ArrayList idSet;                // store foreign key finger_id


    /**
     * connect to database
     */
    public DBManager()
    {
        try
        {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection(url,"root","");
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
    }

    /**
     * close connection
     */
    public void close()
    {
        try
        {
            con.close();
            preStmt1.close();
            preStmt2.close();
            preStmt3.close();
            preStmt4.close();
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
    }
    /**
     * insert hashes information of a song into database
     * @param hashes
     * @param name
     */
    public void insertData(ArrayList<ShazamHash> hashes, String name)
    {
        sql_song = "INSERT INTO song(name) VALUES(?)";
        sql_finger = "INSERT IGNORE INTO finger(f1,f2,dt) VALUES(?,?,?)";
        sql_songfinger = "INSERT INTO finger_song(song_id,finger_id,offset) VALUES(?,?,?)";
        sql_select = "SELECT finger_id FROM finger WHERE f1=? AND f2=? AND dt=?";
        idSet = new ArrayList();

        try
        {
            preStmt1 = con.prepareStatement(sql_song);
            preStmt2 = con.prepareStatement(sql_finger);
            preStmt3 = con.prepareStatement(sql_songfinger);
            preStmt4 = con.prepareStatement(sql_select);

            // insert info into table song
            con.setAutoCommit(true);
            preStmt1.setString(1,name);
            preStmt1.executeUpdate();
            preStmt1 = con.prepareStatement(sql);
            resultSet = preStmt1.executeQuery();
            resultSet.next();
            song_id = resultSet.getInt(1);
            resultSet.close();

            // insert info into table finger
            con.setAutoCommit(false);
            for(int i = 0; i < hashes.size(); i++)
            {
                preStmt2.setInt(1,hashes.get(i).f1);
                preStmt2.setInt(2,hashes.get(i).f2);
                preStmt2.setInt(3,hashes.get(i).dt);
                preStmt2.addBatch();
            }
            // batch process
            preStmt2.executeBatch();
            con.commit();

            // get foreign key info from table finger
            for(int i = 0; i < hashes.size(); i++)
            {
                preStmt4.setInt(1,hashes.get(i).f1);
                preStmt4.setInt(2,hashes.get(i).f2);
                preStmt4.setInt(3,hashes.get(i).dt);
                resultSet = preStmt4.executeQuery();
                resultSet.next();
                idSet.add(resultSet.getInt(1));
            }

            // insert info into table finger_song
            for(int i = 0; i < hashes.size(); i++)
            {
                preStmt3.setInt(1,song_id);
                preStmt3.setInt(2,(int)idSet.get(i));
                preStmt3.setInt(3,hashes.get(i).offset);
                preStmt3.addBatch();
            }
            // batch process
            preStmt3.executeBatch();
            con.commit();
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
    }// end method insertData

    /**
     * This method will return a ResultSet which contains all the hashes matching parameter hash.
     * @param hash
     * @return
     */
    public ResultSet matchHash(ShazamHash hash) throws RuntimeException
    {
        sql_select = "SELECT finger_id FROM finger WHERE f1=? AND f2=? AND dt=?";
        sql_select2 = "SELECT song_id,offset FROM finger_song WHERE finger_id=?";

        try
        {
            preStmt1 = con.prepareStatement(sql_select);
            preStmt2 = con.prepareStatement(sql_select2);
            con.setAutoCommit(true);

            // get the finger_id that matches param hash
            preStmt1.setInt(1,hash.f1);
            preStmt1.setInt(2,hash.f2);
            preStmt1.setInt(3,hash.dt);
            resultSet = preStmt1.executeQuery();
            if(!resultSet.next())
            {
                throw new RuntimeException();
            }
            finger_id = resultSet.getInt(1);

            // get all the hashes whose finger_id equals what we get from last step
            preStmt2.setInt(1,finger_id);
            resultSet = preStmt2.executeQuery();

            return resultSet;
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }

        return null;
    }
}
